package DAO.MysqlDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import DAO.InterfaceDAO.CompteDAO;
import Model.Compte;

public class CompteDAO_Mysql implements CompteDAO{

	private PreparedStatement psExistEtud, psExistProf, psIdEtud, psIdProf, psIsEtudiant, psIsProf, psIsAdmin, psIdAdmin, psExistAdmin;

	public CompteDAO_Mysql(Connection cx) {

		try {
			psExistEtud = cx.prepareStatement("SELECT count(*) as exist FROM etudiant WHERE login=? AND password=? limit 1");
			psExistProf = cx.prepareStatement("SELECT count(*) as exist FROM professeur WHERE login=? AND password=? limit 1");
			psIdEtud = cx.prepareStatement("SELECT id FROM etudiant WHERE login=? AND password=?");
			psIdProf = cx.prepareStatement("SELECT id FROM professeur WHERE login=? AND password=?");
			psIsEtudiant = cx.prepareStatement("SELECT count(*) as isEtud FROM etudiant WHERE id=? AND login=? AND password=?");
			psIsProf = cx.prepareStatement("SELECT count(*) as isProf FROM professeur WHERE id=? AND login=? AND password=?");
			psIsAdmin = cx.prepareStatement("SELECT count(*) as isAdmin FROM administrateur WHERE id=? AND login=? AND password=?");
			psIdAdmin = cx.prepareStatement("SELECT id FROM administrateur WHERE login=? AND password=?");
			psExistAdmin = cx.prepareStatement("SELECT count(*) as exist FROM administrateur WHERE login=? AND password=? limit 1");
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	@Override
	public boolean create(Compte a) {
		return false;
	}

	@Override
	public Compte read(String id) {
		return null;
	}

	@Override
	public boolean update(Compte a) {
		return false;
	}

	@Override
	public boolean delete(Compte a) {
		return false;
	}

	@Override
	public Compte read(String login, String password) {
		return null;
	}

	@Override
	public boolean exist(String login, String password) {
		try {
			psExistEtud.setString(1, login);
			psExistEtud.setString(2, password);
			ResultSet rs = psExistEtud.executeQuery();
			if (rs.next() && rs.getInt("exist")==1) {
				return true;
			} else {
				psExistProf.setString(1, login);
				psExistProf.setString(2, password);
				rs = psExistProf.executeQuery();
				if (rs.next() && rs.getInt("exist")==1) {
					return true;
				}else {


					psExistAdmin.setString(1, login);
					psExistAdmin.setString(2, password);
					rs = psExistAdmin.executeQuery();
					if (rs.next() && rs.getInt("exist")==1) {
						return true;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}

	@Override
	public int getId(String login, String password) {
		try {
			psIdEtud.setString(1, login);
			psIdEtud.setString(2, password);
			ResultSet rs = psIdEtud.executeQuery();
			if (rs.next()) {
				return rs.getInt("id");
			} else {
				psIdProf.setString(1, login);
				psIdProf.setString(2, password);
				rs = psIdProf.executeQuery();
				if (rs.next()) {
					return rs.getInt("id");
				} else {
					psIdAdmin.setString(1, login);
					psIdAdmin.setString(2, password);
					rs = psIdAdmin.executeQuery();
					if (rs.next()) {
						return rs.getInt("id");
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return -1;
	}

	@Override
	public boolean isEtudiant(int idCompte,String login, String password) {
		try {
			psIsEtudiant.setInt(1, idCompte);
			psIsEtudiant.setString(2,login);
			psIsEtudiant.setString(3,password);
			ResultSet rs = psIsEtudiant.executeQuery();
			if (rs.next()) {
				return rs.getInt("isEtud")==1;
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}

	@Override
	public boolean isProf(int idCompte,String login, String password) {
		try {
			psIsProf.setInt(1, idCompte);
			psIsProf.setString(2, login);
			psIsProf.setString(3, password);
			ResultSet rs = psIsProf.executeQuery();
			if(rs.next()) {
				return rs.getInt("isProf")==1;
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}

	@Override
	public boolean isAdmin(int idCompte,String login, String password) {
		try {
			psIsAdmin.setInt(1, idCompte);
			psIdAdmin.setString(2, login);
			psIdAdmin.setString(3, password);
			ResultSet rs = psIsAdmin.executeQuery();
			if(rs.next()) {
				return rs.getInt("isAdmin")==1;
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}

}
